libname libone 'C:\Users\m40lxz1\OneDrive - Northern Illinois University\quality\sas\sasdata\stability';
libname libtwo 'C:\Users\m40lxz1\OneDrive - Northern Illinois University\quality\sas\sasdata';

/* This program examines the frequency and magnitude of rating changes, upgrades and downgrades.  */

data mdy;
set libone.mdy_rating_history_corrected;
where year(rad) ge 2013 and year(rad) lt 2021;
drop cik;
if ratechange=0 or ratechange=. then delete;

data mdy;
 set mdy;
 if ratechange gt 1 and ratechange ne . then multidown=1; else multidown=0;
 if ratechange lt -1 and ratechange ne . then multiup=1; else multiup=0;



data mdy;
 set mdy;
 year=year(rad);

proc sort data=mdy;
 by rac_correct;run;

proc means data=mdy n mean median p1 p99 min max;
 var ratechange multidown multiup change_days ;
 by rac_correct;



proc means data=mdy n mean median p1 p99 min max;
 var  change_days ;

 run;

data sp;
set libone.sp_rating_history_corrected;
where year(rad) ge 2013 and year(rad) lt 2021;
drop cik;
if ratechange=0 or ratechange=. then delete;

data sp;
 set sp;
 if ratechange gt 1 and ratechange ne . then multidown=1; else multidown=0;
 if ratechange lt -1 and ratechange ne . then multiup=1; else multiup=0;

data sp;
 set sp;
 year=year(rad);

proc sort data=sp;
 by rac_correct;run;

proc means data=sp n mean median p1 p99 min max;
 var ratechange multidown multiup change_days ;
 by rac_correct; run;

proc means data=sp n mean;
var change_days;run;

data fitch;
set libone.fitch_rating_history_corrected;
where year(rad) ge 2013 and year(rad) lt 2021;
drop cik;
if ratechange=0 or ratechange=. then delete;

data fitch;
 set fitch;
 if ratechange gt 1 and ratechange ne . then multidown=1; else multidown=0;
 if ratechange lt -1 and ratechange ne . then multiup=1; else multiup=0;

data fitch;
 set fitch;
 year=year(rad);

proc sort data=fitch;
 by rac_correct;run;

proc means data=fitch n mean median p1 p99 min max;
 var ratechange multidown multiup change_days ;
 by rac_correct;
run;


proc means data=fitch n mean;
var change_days;run;

data ejr;
set libone.ejr_rating_history_corrected;
where year(rad) ge 2013 and year(rad) lt 2021;
drop cik;
if ratechange=0 or ratechange=. then delete;

data ejr;
 set ejr;
 if ratechange gt 1 and ratechange ne . then multidown=1; else multidown=0;
 if ratechange lt -1 and ratechange ne . then multiup=1; else multiup=0;

data ejr;
 set ejr;
 year=year(rad);

proc sort data=ejr;
 by rac_correct;run;

proc means data=ejr n mean median p1 p99 min max;
 var ratechange multidown multiup change_days ;
 by rac_correct;
run;

proc means data=EJR n mean;
var change_days;run;



/*************************************************************************************************************/

data all; 
 set sp mdy fitch ejr;

ods graphics off;

proc ttest data=all;
 class ran;
  var change_days;
  where ran='EJR' or ran='Fitch';

run;
  
proc ttest data=all;
 class ran;
  var ratechange multiup ;
  where (ran='EJR' or ran='Fitch') and rac_correct='UP';
 run;

   
proc ttest data=all;
 class ran;
  var ratechange multidown ;
  where (ran='EJR' or ran='Fitch') and rac_correct='DG';
 run;


/***************************************************************************************************************/

* The next two steps get the SP rating change information for SP-MDY joint rated issues; 
 
proc sql;
 create table sp_mdy as
 select A.*
 from sp as A
 inner join libtwo.mdy_start_end_year as B
 on A.id=B.id and A.year ge B.firstyear and A.year le B.lastyear;;

  title 'SP rating change for SP-MDY joint rated';

 proc means data=sp_mdy n mean median std min max;
 var ratechange multidown multiup change_days;
 where year ge 2013 and year lt 2021; 
 by rac_correct;run;

 proc means data=sp_mdy n mean;
var change_days;run;

* by year;run;


proc sql;
 create table mdy_sp as
 select A.*
 from mdy as A
 inner join libtwo.sp_start_end_year as B
 on A.id=B.id and A.year ge B.firstyear and A.year le B.lastyear;

  title 'MDY rating change for SP-MDY joint rated';

 proc means data=mdy_sp n mean median std min max;
 var ratechange multidown multiup change_days;
 by rac_correct;
* by year;run;

proc means data=mdy_sp n mean;
var change_days;run;


data sp_mdy_all;
 set mdy_SP sp_mdy;

ods graphics off;

proc ttest data=sp_mdy_all;
 class ran;
 var change_days;run;

 
data sp_mdy_all;
 set sp_mdy_all;
 if ran='SP' then SP=1; else SP=0;run;

proc genmod data=sp_mdy_all;
 class id;
 model multidown=SP /dist=normal;
 repeated sub=id/type=ind;
 where rac_correct='DG';
 run;

proc genmod data=sp_mdy_all;
 class id;
 model ratechange=SP /dist=normal;
 repeated sub=id/type=ind;
 where rac_correct='UP';
 run;

 proc sort data=sp_mdy_all;
  by rac_correct;

 proc ttest data=sp_mdy_all;
 class ran;
 var ratechange multidown multiup;
 by rac_correct;run;


proc sql;
 create table sp_fitch as
 select A.*
 from sp as A
 inner join libtwo.fitch_start_end_year as B
 on A.id=B.id and A.year ge B.firstyear and A.year le B.lastyear;;

  title 'SP rating change for SP-Fitch joint rated';

 proc means data=sp_fitch n mean median std min max;
 var ratechange multidown multiup change_days;
 where year ge 2013 and year lt 2021; 
 by rac_correct;run;

proc means data=sp_fitch n mean;
var change_days;run;

* by year;run;

proc sql;
 create table fitch_sp as
 select A.*
 from fitch as A
 inner join libtwo.sp_start_end_year as B
 on A.id=B.id and A.year ge B.firstyear and A.year le B.lastyear;;

 title 'Fitch rating change for SP-Fitch joint rated';

 proc means data=fitch_sp n mean median std min max;
 var ratechange multidown multiup change_days;
 by rac_correct;
* by year;run;

proc means data=fitch_sp n mean;
var change_days;run;


data sp_fitch_all;
 set sp_fitch fitch_sp;

ods graphics off;

proc ttest data=sp_fitch_all;
 class ran;
 var change_days;run;

 proc sort data=sp_fitch_all;
  by rac_correct;

 proc ttest data=sp_fitch_all;
 class ran;
 var ratechange multidown multiup;
 by rac_correct;run;

data sp_fitch_all;
 set sp_fitch_all;
 if ran='SP' then SP=1; else SP=0;run;


proc genmod data=sp_fitch_all;
 class id;
 model multidown=SP /dist=normal;
 repeated sub=id/type=ind;
 where rac_correct='DG';
 run;

proc genmod data=sp_fitch_all;
 class id;
 model multiup=SP /dist=normal;
 repeated sub=id/type=ind;
 where rac_correct='UP';
 run;

proc genmod data=sp_fitch_all;
 class id;
 model ratechange=SP /dist=normal;
 repeated sub=id/type=ind;
 where rac_correct='DG';
 run;

 proc genmod data=sp_fitch_all;
 class id;
 model change_days=SP /dist=normal;
 repeated sub=id/type=ind;

 run;

 /*************************************************************************************************************/

proc sql;
 create table sp_ejr as
 select A.*
 from sp as A
 inner join libtwo.ejr_start_end_year as B
 on A.id=B.id and A.year ge B.firstyear and A.year le B.lastyear;;

  title 'SP rating change for SP-EJR joint rated';

 proc means data=sp_ejr n mean median std min max;
 var ratechange multidown multiup change_days;
 where year ge 2013 and year lt 2021; 
 by rac_correct;run;

 proc means data=sp_ejr n mean;
var change_days;run;

* by year;run;

proc sql;
 create table EJR_sp as
 select A.*
 from EJR as A
 inner join libtwo.sp_start_end_year as B
 on A.id=B.id and A.year ge B.firstyear and A.year le B.lastyear;;

 title 'EJR rating change for SP-EJR joint rated';

 proc means data=EJR_sp n mean median std min max;
 var ratechange multidown multiup change_days;
 by rac_correct;
* by year;run;

proc means data=EJR_sp n mean;
var change_days;run;



data sp_ejr_all;
 set sp_ejr ejr_sp;

ods graphics off;

proc ttest data=sp_ejr_all;
 class ran;
 var change_days;run;

 proc sort data=sp_ejr_all;
  by rac_correct;

 proc ttest data=sp_ejr_all;
 class ran;
 var ratechange multidown multiup;
 by rac_correct;run;

 
data sp_ejr_all;
 set sp_ejr_all;
 if ran='SP' then SP=1; else SP=0;run;

proc genmod data=sp_ejr_all;
 class id;
 model multidown=SP /dist=normal;
 repeated sub=id/type=ind;
 where rac_correct='DG';
 run;

proc genmod data=sp_ejr_all;
 class id;
 model multiup=SP /dist=normal;
 repeated sub=id/type=ind;
 where rac_correct='UP';
 run;

proc genmod data=sp_ejr_all;
 class id;
 model ratechange=SP /dist=normal;
 repeated sub=id/type=ind;
 where rac_correct='UP';
 run;

 proc genmod data=sp_ejr_all;
 class id;
 model change_days=SP /dist=normal;
 repeated sub=id/type=ind;

 run;


/*********************************************************************************************************************/
* The next two steps get the MDY rating change information for MDY-Fitch joint rated issues; 
 
proc sql;
 create table mdy_fitch as
 select A.*
 from mdy as A
 inner join libtwo.fitch_start_end_year as B
 on A.id=B.id and A.year ge B.firstyear and A.year le B.lastyear;;

 title 'Moody rating change for MDY-Fitch joint rated';

 proc means data=mdy_fitch n mean median std min max;
 var ratechange multidown multiup change_days;
* by rac_correct;
* by year;run;

proc means data=mdy_fitch n mean;
var change_days;run;

proc sql;
 create table fitch_mdy as
 select A.*
 from fitch as A
 inner join libtwo.mdy_start_end_year as B
 on A.id=B.id and A.year ge B.firstyear and A.year le B.lastyear;;

 title 'Fitch rating change for MDY-Fitch joint rated';

 proc means data=fitch_mdy n mean median std min max;
 var ratechange multidown multiup change_days;
 *by rac_correct;
* by year;run;

proc means data=fitch_mdy n mean;
var change_days;run;



data mdy_fitch_all;
 set mdy_fitch fitch_mdy;

ods graphics off;

proc ttest data=mdy_fitch_all;
 class ran;
 var change_days;run;


 proc sort data=mdy_fitch_all;
  by rac_correct;

 proc ttest data=mdy_fitch_all;
 class ran;
 var ratechange multidown multiup;
 by rac_correct;run;

 
data mdy_fitch_all;
 set mdy_fitch_all;
 if ran='MDY' then MDY=1; else MDY=0;run;

proc genmod data=mdy_fitch_all;
 class id;
 model multidown=MDY /dist=normal;
 repeated sub=id/type=ind;
 where rac_correct='DG';
 run;

proc genmod data=mdy_fitch_all;
 class id;
 model multiup=MDY /dist=normal;
 repeated sub=id/type=ind;
 where rac_correct='UP';
 run;

proc genmod data=mdy_fitch_all;
 class id;
 model ratechange=MDY /dist=normal;
 repeated sub=id/type=ind;
 where rac_correct='UP';
 run;

 proc genmod data=mdy_fitch_all;
 class id;
 model change_days=MDY /dist=normal;
 repeated sub=id/type=ind;

 run;
/***********************************************************************************************************************/
proc sql;
 create table mdy_ejr as
 select A.*
 from mdy as A
 inner join libtwo.ejr_start_end_year as B
 on A.id=B.id and A.year ge B.firstyear and A.year le B.lastyear;;

 title 'Moody rating change for MDY-EJR joint rated';

 proc means data=mdy_ejr n mean median std min max;
 var ratechange multidown multiup change_days;
 by rac_correct;
* by year;run;

proc means data=mdy_ejr n mean;
var change_days;run;

proc sql;
 create table EJR_mdy as
 select A.*
 from EJR as A
 inner join libtwo.mdy_start_end_year as B
 on A.id=B.id and A.year ge B.firstyear and A.year le B.lastyear;;

 title 'EJR rating change for MDY-EJR joint rated';

 proc means data=EJR_mdy n mean median std min max;
 var ratechange multidown multiup change_days;
 by rac_correct;
* by year;run;

proc means data=EJR_MDY n mean;
var change_days;run;


data mdy_EJR_all;
 set mdy_EJR EJR_mdy;

ods graphics off;

proc ttest data=mdy_EJR_all;
 class ran;
 var change_days;run;

 proc sort data=mdy_EJR_all;
  by rac_correct;

 proc ttest data=mdy_EJR_all;
 class ran;
 var ratechange multidown multiup;
 by rac_correct;run;



 
data mdy_ejr_all;
 set mdy_ejr_all;
 if ran='MDY' then MDY=1; else MDY=0;run;

proc genmod data=mdy_ejr_all;
 class id;
 model multidown=MDY /dist=normal;
 repeated sub=id/type=ind;
 where rac_correct='DG';
 run;

proc genmod data=mdy_ejr_all;
 class id;
 model multiup=MDY /dist=normal;
 repeated sub=id/type=ind;
 where rac_correct='UP';
 run;

proc genmod data=mdy_ejr_all;
 class id;
 model ratechange=MDY /dist=normal;
 repeated sub=id/type=ind;
 where rac_correct='DG';
 run;

 proc genmod data=mdy_ejr_all;
 class id;
 model change_days=MDY /dist=normal;
 repeated sub=id/type=ind;

 run;

 /**********************************************************************************************************************/


proc sql;
 create table Fitch_ejr as
 select A.*
 from fitch as A
 inner join libtwo.ejr_start_end_year as B
 on A.id=B.id and A.year ge B.firstyear and A.year le B.lastyear;;

 title 'Fitch rating change for Fitch-EJR joint rated';

 proc means data=Fitch_ejr n mean median std min max;
 var ratechange multidown multiup change_days;
 by rac_correct;
* by year;run;

proc means data=fitch_ejr n mean;
var change_days;run;

proc sql;
 create table EJR_Fitch as
 select A.*
 from EJR as A
 inner join libtwo.Fitch_start_end_year as B
 on A.id=B.id and A.year ge B.firstyear and A.year le B.lastyear;;

 title 'EJR rating change for Fitch-EJR joint rated';

 proc means data=EJR_Fitch n mean median std min max;
 var ratechange multidown multiup change_days;
 by rac_correct;
* by year;run;

proc means data=ejr_fitch n mean;
var change_days;run;


data Fitch_EJR_all;
 set Fitch_EJR EJR_Fitch;

ods graphics off;

proc ttest data=Fitch_EJR_all;
 class ran;
 var change_days;run;

 proc sort data=fitch_EJR_all;
  by rac_correct;

 proc ttest data=fitch_EJR_all;
 class ran;
 var ratechange multidown multiup;
 by rac_correct;run;

 
data fitch_ejr_all;
 set fitch_ejr_all;
 if ran='Fitch' then Fitch=1; else Fitch=0;run;

proc genmod data=fitch_ejr_all;
 class id;
 model multidown=fitch /dist=normal;
 repeated sub=id/type=ind;
 where rac_correct='DG';
 run;

proc genmod data=fitch_ejr_all;
 class id;
 model multiup=fitch /dist=normal;
 repeated sub=id/type=ind;
 where rac_correct='UP';
 run;

proc genmod data=fitch_ejr_all;
 class id;
 model ratechange=fitch /dist=normal;
 repeated sub=id/type=ind;
 where rac_correct='DG';
 run;

 proc genmod data=fitch_ejr_all;
 class id;
 model change_days=fitch /dist=normal;
 repeated sub=id/type=ind;

 run;
